package com.crm.dao;

import java.util.Iterator;
import java.util.List;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.Transaction;

import com.crm.entity.DeliveryChallan;
import com.crm.entity.PO;
import com.crm.util.ApplicationContext;

public class PendingOrdersDAO {
	protected final Log logger = LogFactory.getLog(getClass());
	
	public  List<PO> getPurchaseOrders(int busid) throws  Exception{
		Session session =  ApplicationContext.getHiberSession().openSession();
		try {
			String query="SELECT DISTINCT ENQ_ID FROM PO WHERE ENQ_ID!=0 and business_id=:busid";
			Query qry=session.createQuery(query);
			qry.setInteger("busid", busid);
			List<PO> lipo=qry.list();
			return lipo;
		}
		catch (Exception e) {
			throw e;
		}
		finally {
			session.close();
		}
	}
	public  List<DeliveryChallan> getDeliverChallan(int busid) throws Exception{
		Session session =  ApplicationContext.getHiberSession().openSession();
		try {
			String query="SELECT DISTINCT QUOTID FROM DELIVERYCHALLAN WHERE QUOTID!=0 and business_id=:busid;";
			Query qry=session.createQuery(query);
			qry.setInteger("busid", busid);
			List<DeliveryChallan> lidc=qry.list();
			return lidc;
		}
		catch (Exception e) {
			throw e;
		}
		finally {
			session.close();
		}
	}
	public  List<String> getPendingOrders(int busid) throws Exception{
		Session session =  ApplicationContext.getHiberSession().openSession();
		try {
			String query="SELECT DISTINCT p.enquiryId FROM PO p WHERE p.enquiryId!=0 AND p.enquiryId NOT IN(SELECT DISTINCT q.enqid FROM Quotation q WHERE q.id IN(SELECT DISTINCT dc.quotid FROM DeliveryChallan dc WHERE dc.quotid!=0 and business_id=:busid))";
			Query qry=session.createQuery(query);
			qry.setInteger("busid", busid);
			List<String> lipo=qry.list();
			return lipo;
		}
		catch (Exception e) {
			throw e;
		}
		finally {
			session.close();
		}
	}
	public  PO findById(int id,int busid) throws Exception{
		Session session =  ApplicationContext.getHiberSession().openSession();
		try {
			String query="from PO where enq_id=:id and business_id=:busid";
			Query qry=session.createQuery(query);
			qry.setInteger("busid", busid);
			qry.setInteger("id", id);
			List<PO> lipo=(List<PO>) qry.list();
			Iterator itr=lipo.iterator();
			PO p=null;
			while(itr.hasNext()){
				Object ob=itr.next();
				p=(PO)ob;
			}
			return p;
		}
		catch (Exception e) {
			throw e;
		}
		finally {
			session.close();
		}
	}
	public  void updateRemarks(int id,String rem,int busid) throws Exception{
		Session session =  ApplicationContext.getHiberSession().openSession();
		Transaction tx=null;
		try {
			tx = session.beginTransaction();
			Query query=session.createQuery("update PO set remarks=:rem where id=:id and business_id=:busid");
			query.setInteger("id", id);
			query.setString("rem", rem);
			query.setInteger("busid", busid);
			query.executeUpdate();
			tx.commit();
			return;
		}
		catch (Exception e) {
			if (tx!=null) tx.rollback();
			throw e;
		}
		finally {
			session.close();
		}
	}

}
